The main usage of SSAS is to build OLAPS / online analytical processing. The basic idea To create OLAP is to trade increased Storage space for Speed of Querying. OLAP does This by pre-calculating and storing aggregates when you identify the data that you want To store in an OLAP Database, analysis Services analyze it in advance.
The Basic Concepts of OLAP
Cube :
The Basic Unit of Storage and analysis Services is the cube.
A cube is a collection of data that is been aggregated to allow Queries to return data quickly
Cubes are ordered into Dimensions and measures. Dimensions come from dimension Table measures that come from the next table.
Dimension Table :
It contains hierarchical data by Which you would like to summarize.
For example :
An order table that you might group by year, month, week, and day of receipt.
Dimension:
Each cube has one (or) more dimensions. Each dimension based on one (or) More dimension tables.
Fast table :
If contains basic information that you want to summarize.
Note:
Any table that you have used with SUM, the Average function in a total query is a good bet To be a fact table.
Measure:
Each cube will Contain one (or) more measures. Measures is a key value to analysis (or) to evaluate the performance of the organization.
Schema:
There are two types of schema in OLAP
In snowflake schema, Some dimension tables are related indirectly to the fact table.
Steps to Configure (or) Create a Data cube :
To build a new data cube using the business intelligence development studio.We need to perform the following steps:
At TekSlate, we offer resources that help you in learning various IT courses. We avail both written material and demo video tutorials. To gain in-depth knowledge and be on par with practical experience, then explore MSBI Training.
Create a new analysis Service project :
Open Business Intelligence Development Studio ------------>
Select File Menu ------------>
New ------------>
Project ------------>
Select project Analysis Services ------------>
Change project name and location ------------>
Click ok
Define a Data Source:
In Solution Explorer select Data Source ------------>
Right-click and select ‘New Data source’ ------------>
Welcome to the Data Source Wizard ------------>
Click Next ------------>
Click New to create a new connection Manager and set, ------------>
Server name – localhost ------------>
Database name – Adventure Works D W ------------>
Click ok ------------>
Click Next ------------>
Check default Radio button ------------>
Click next ------------>
Data Source Name - product details ------------>
Click Finish
Define a Data Source view:
In Solution Explorer select Data Source ------------>
Right-click and select New Data source View ------------>
In Welcome to the Data Source Wizard ------------>
Click Next ------------>
Select the available Relational Data source [Product details] ------------>
Click Next ------------>
Fact Resellers Sales table from available object list ------------>
Click Add Related tables to include all the tables Which are Referenced Sales table ------------>
Click Next ------------>
Set, ------------>
Name - Fact Resellers View ------------>
Click Finish ------------>
Cube:
In Solution Explorer select cube ------------>
Right-click and select New cube ------------>
In Welcome to the Cube Wizard, click Next ------------>
In the Select Build method page, Check to Build the cube using a data source radio button and also Make Sure that the Auto build Checkbox is checked. To create attributes and hierarchy’s from The Specified dimension table. ------------>
Click Next ------------>
In the Select Data source view page, Select the available Data source view named as product Information [Select the data source view that will provide the data for the cube] ------------>
Click Next ------------>
Click Next ------------>
In identify facts and dimension tables Page select a dimension table from the drop-down list ------------>
Click next ------------>
In the Select time period page select any Column [time Key] to create hierarchy’s on-time dimension ------------>
Click next ------------>
Click Next ------------>
Click Next ------------>
Click Finish
Process the cube :
Select project [ In Solution Explorer] ------------>
Right-click and select process option ------------>
Click Yes ------------>
In-Process Database Wizard, click Run ------------>
Click close ------------>
Open SQL Server Management Studio (SSMS) ------------>
Click connect and set, ------------>
Server type - Analysis Server ------------>
Server Name – Localhost ------------>
Click connect
Exploring the Data cube:
In BIDS Create a Cube and process (or) deploy it to Server. BIDS includes a Built-in cube Browser that lets you explore the data in any cube that has been deployed (or) Processed. To open the cube Browser. In Solution Explorer double click on cube.[Product information ]
Select browse tab ------------>
Drag and Drop the column on columns (or) Row (or) Filter area.
Introduction to MDX
Multi Dimension expression (MDX) is a language created to allow the users to Work With multidimensional data in Microsoft SQL Server Analysis Service. It Works as a Query language with OLAP (Online Analytical Processing) Cubes. We can also use It to Create calculated members or use the various function provided which can be Used to query data.
Differences in SQL in MDX
If you are a little familiar with SQL (Structured Query Language), You may fell there Is some similarity with MDX in some aspects. The SELECT clause, FROM clause, and WHILE clause are similar to SQL and MDX. MDX Provides various ways with Which A cube can be queried it provides different functions with MDX we can even create, Modify and delete cubes of require
Syntax of MDX Query (Multi Dimension Expression):
WITH
MEMBER exp 1 and exp 2
Select
{ - (Set Operator)
<axis – Specification 1>,
<axis – Specification 2>,
} ON Columns
<axis – Specification > on Rows
FROM [ cube Name]
Where [Slicer]
Axis Specification :
A collection of members from different dimensions Organized as a set of types [columns]
Members :
Select dimension attribute that are included in output cube.
Slicer :
It is a filter that Selects cells in the output cube.
Set Operator [{}] :-
The set operator is used to display a collection of members On a single axis. In MDX Set Operator is {} (or) Generate ()
NOTE:
Generate MDX Function is a set of members
Functions in MDX:
Order function:
It is used to sort the data either in ascending (or) descending order.
Top Count :
It is used to display a specified number of rows from the top.
For example:
Top count ([Dim product].[English product Name]. all member, 5) or rows.
Bottom Count :
It is used to display a specified no. of Records from the Bottom.
For example :
Bottom count ([Dim product].[English product Name]. all member, 5)
Comma Operator:
it is used to construct a set by Enumerating types. For example :
Select{
[measures].[order Quantity],
[measures].[order Amount],
[measures].[order Amount],
} on columns
It is used to specify a range with discrete (or) Continuous data.
For example:
Where [Fact Internet Sales – Due Data] . [Time key] & [i] :
[Fact Internet Sales – Due Data] . [Time key] & [15]
.Dot Operator :
It is used to return a set of all the numbers from the specified dimensions
For ex:
[Dim Product] .[English Product Name]. all members on rows
With operator:
It is used to specify Calculated measures
fore x:-
With Member [Measures].[total amount] as
[Measures].[sales amount] *
[Measures] .[Order Quantity]
NON EMPTY :
It is used to Remove null Records either from columns (or) Rows.
For ex: Select nonempty
{[Measures] .[order Quantity]} on columns non empty
[Dim product] . [English product Name] all members on rows.
Comments :
- - ==>
/* * / ==> for multi
MDX notation for schema:
[Dim time].[Fiscal].[2011].[Q 1].[April]
[Dim table] – Dimension
[Dim Time].[Fiscal] - Hierarchy
[Dim Time].[Fiscal].[2011].[Q 1] - Level
[Dim Time].[Fiscal].[2011].[Q 1].[April] - Member Select non empty
{
[measures]. [order Quantity]. [sales amount]
} on columns
Non Empty
[Dim Product] . [English product Name] . all members on rows
From [product information] the above MDX Query Returns all the English product
Names on Rows and its Corresponding order Quantity and Sales amount displayed on Rows
From the specified cube called product information and it Return non Empty (Not Null) Records
WITH
MEMBER [Measures] .[total amount] as
[Measures] .[sales amount] *
[Measures] .[order Quantity]
Format – String = “$ # #, # # #. # #”
Select non Empty
{
[Measures] .[ order Quantity] , [sales amount], [total Amount ]
} on columns
Non Empty
[Dim Product]. [English Product Name] . all members on rows
From [Product information]
The above MDX Query New measures is calculated [measures].[total amount]
And its formatted as with two decimal digits .
Select non Empty
{
[Measures] .[ order Quantity] , [sales amount], [total Amount ]
} on columns ,
Non Empty
[Dim Product]. [English Product Name] . all members on rows
From [Product information]
In the above MDX Query Returns all the English product names on rows and its corresponding order Quantity and sales amount based on the Where clause [time key start from 1 to 15 th]
Format – String : It is used to format the measures in WITH Operator [format the calculated measures]
For ex : -
Format – String = “$ # #, # # #. # #”
Format – String n = “Currency”
Format – String 1 = “Percent”
Calculations
KPI (Key Performance Indication)
How to Calculate calculated measures using BIDA Wizard :
Open BIDS ------------>
Create a new cube ------------>
In cube , Select calculations tab ------------>
Click on New Calculated member and set
Name - [total amount]
Parent Hierarchy - select MEASURES from drop down list
Expression - Define the following Expression by drag and drop the
Measures from the measures Section (from fact table)
[measures] . [Sales Amount ]
[measures] . [tax Amount] ------------>
Format String - Select “Currency” option
Color – click and select any color
Back ground Color - Click and Select any color ------------>
Click ok ------------>
In Calculating tab Click process to deploy (or) process in to analysis Services Server
Select Browser tab Expand measures you Would observed that the total amount ------------>
Calculated measure is available.
KPIS (Key Performance Indications)
In Analysis Services, Represent Data to measure Business Success at very high
Level (mostly at Organization level (or) Regional level)
Learn more about MSBI Interview Questions in this blog post.
Steps to Configure KPIS in a cube:
Open BIDS ------------>
Create a valid cube ------------>
Select KPIs tab in a cube ------------>
Click on New KPI and It will open one template for filling the desired information ,
Name - Provide any name to new KPI like Fact internet sales KPI
Associated measure group – KPI belongs to one particular Here you can Specify
A particular measure group (or) you can specify all.
Value Expression :
This is a actual value of KPI this may be a row measure (or) MDX numeric
Expression . Drag and Drop the following Expression to KPI Value
[Measures].[order Quantity]
Goal Expression : Here our new crated measure will come in to picture. We Get value Expression from measure group (order Quantity)
But the goal value will be target measure groups.
Provide the following Expression as target (or) goal Expression.
[Measures]. [Total product Cost]
Status: It’s check the state of KPI at any point Status MDX Expression should
Return value between -1 to +1.
Provide the following Expression to find out the Status of the KPI
IIF (KPI VALUE (“[Measures]. [order Quantity]”)) > KPI GOAL
(“[Measures]. [Total product cost]”),-1,1)
In the above example if KPI value greater than KPI Goal then the status
Is negative (Bad performance) (or) else positive (good performance).
How to generate a Report from Analysis Services Cube:
Open BIDS ------------>
In Analysis Service Create a valid cube and deploy it to analysis Service Server
Open BIDS Create Report Server project ------------>
In Solution Explorer set shared Data sources ------------>
Right click and select Add new Data source and Set,
Name - D src product sales details
Type - Select Microsoft SQL Server Analysis Server option from drop down list
Check Edit
Server Name - Local host (or) Actual Server name (or) IP Address
Data base Name - Cube Name (Product Details morning 8 : 30) ------------>
Test connection ------------>
Click ok thrice ------------>
Select Report option ------------>
Right click and Select Add ------------>
New Item ------------>
Select Reports template Rename it as product details from cube RDl ------------>
Click add ------------>
In Data tab add new Data set and set
Name – D set product details
Data source – Select D src product sales details
Command type - text ------------>
Click ok
Drag and Drop the following fields from the available Meta data section
[Facts and dimension tables]
From Dim product ,
[English Product Name],
[class],
[color],
[Dealer price],
[List price]
From measure
[measure]. [Fright]
[measure].[unit price]
Press Alt + ctrl + D for Data set ------------>
Go to layout tab design any Report item (table, matrix, check etc..,) ------------>
Now the Report is displaying all the Reports from the specified cube. ------------>
How to Set parameterized Reports from cube:-
From the above Report example in a cube.
Let as Start by creating a cube on adventure works DW data base.
Data base namely ‘Adventure Works DW’.
Of tables Dim product , Dim product sub category, Dim product category,
Dim customer and Fact Internet Sales.
Unit price, Sales Amount and Internet Sales Count . Give cube name as Demo cube.
Select Deployment tab and enter Server name. (if you are working with default)
i.e local host )
Services to work MDX quires.
SELECT FROM Demo cube In this case default member for Order Quantity is
Displayed as 60938.
Enter the query as :
SELECT [Measure]. [order Quantity] ON 0
FROM Demo cube.
Will also give the same result . (0 can also be replaced by COLUMNS) which is the axis.
In this Case We can see two measures Order Quantity and Sales Amount on columns.
SELECT [Product].[Product Category Key].[Bikes]
ON COLUMNS FROM Demo cube.
OR
SELECT [Product].[Product Category Key]. &[1]
ON COLUMNS FROM Demo cube.
SELECT [Product].[Product Category Key]. &[1]
ON COLUMNS.
[Customer].[customer key]. MEMBERS ON ROWS FROM Demo Cube.
With this, we get the Sales Amount for Bikes for all different customers.
This can be achieved by using Ordinals as 0 and 1 instead or COLUMNS and ROWS.
[customer]. [customer key]. MEMBERS ON ROWS
FROM Demo cube.
To get all categories (we added NON-EMPTY to get rid of NULL Values)
WITH MEMBER Measures.[calculated Member] As
([Measures]. [order Quantity] * [Measures] [Unit Price])
SELECT NON EMPTY { Measures . [Calculated Member ] [Measures][Sales Amount] }
ON COLUMNS, NON-EMPTY [product] [Product Category key]. MEMBERS on ROWS
FROM DEMO CUBE
First, we calculate the member and we it with the field to View the result.
SELECT NON EMPTY { Measures . [Calculated Member ] [Measures][Sales Amount] }
ON COLUMNS,
NON EMPTY [product] [Product Category key]. CHILDREN on ROWS
FROM Demo Cube.
We get only the (nonempty) children for categories, all categories are not displayed.
For an In-depth knowledge on MSBI click on:
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
